package com.easybuy.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.easybuy.dao.BaseDao;
import com.easybuy.dao.ProductDao;
import com.easybuy.entity.Product;

public class ProductDaoImpl extends BaseDao implements ProductDao {

	private Connection conn;
	private PreparedStatement pstmt;
	private ResultSet rs;
	
	@Override
	public List<Product> findByCategoryId(int categoryId,int categoryLevel,int startIndex,int pageSize) {
		List<Product> list = new ArrayList<>();
		try {
			conn = super.getConnection();
			String sql = "select id,name,description,price,stock,categoryLevel1Id,categoryLevel2Id,categoryLevel3Id,fileName,isDelete "
					+ "from easybuy_product where isDelete=0 " ;

			switch (categoryLevel) {
			case 1:
				sql += " and categoryLevel1Id=?";
				break;
			case 2:
				sql += " and categoryLevel2Id=?";
				break;
			case 3:
				sql += " and categoryLevel3Id=?";
				break;
			default:
				break;
			}
			sql += " limit ?,?";
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setObject(1, categoryId);
			pstmt.setObject(2, startIndex);
			pstmt.setObject(3, pageSize);
			rs = pstmt.executeQuery();
			
			while (rs.next()) {
				Product product = new Product();
		        product.setId(rs.getInt("id"));
		        product.setName(rs.getString("name"));
		        product.setDescription(rs.getString("description"));
		        product.setPrice(rs.getFloat("price"));
		        product.setStock(rs.getInt("stock"));
		        product.setCategoryLevel1Id(rs.getInt("categoryLevel1Id"));
		        product.setCategoryLevel2Id(rs.getInt("categoryLevel2Id"));
		        product.setCategoryLevel3Id(rs.getInt("categoryLevel3Id"));
		        product.setFileName(rs.getString("fileName"));
				list.add(product);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
        	super.closeAll(conn, pstmt, rs);
        }
		return list;
	}
	
	public int findCountByCategoryId(int categoryId,int categoryLevel) {
		int count=0;
		try {
			conn = super.getConnection();
			String sql = "select count(1) "
					+ "from easybuy_product where isDelete=0 " ;

			switch (categoryLevel) {
			case 1:
				sql += " and categoryLevel1Id=?";
				break;
			case 2:
				sql += " and categoryLevel2Id=?";
				break;
			case 3:
				sql += " and categoryLevel3Id=?";
				break;
			default:
				break;
			}
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setObject(1, categoryId);
			rs = pstmt.executeQuery();
			
			if (rs.next()) {
				count = rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
        	super.closeAll(conn, pstmt, rs);
        }
		
		return count;
		
	}
	
	public static void main(String[] args) {
		ProductDaoImpl dao = new ProductDaoImpl();
		List<Product> list = dao.findByCategoryId(548, 1, 0, 4);
		for (Product product : list) {
			System.out.println(product.getName());
		}
	}

	@Override
	public Product findById(int id) {
		Product product = null;
		
		try {
			conn = super.getConnection();
			String sql = "select id,name,description,price,stock,categoryLevel1Id,categoryLevel2Id,categoryLevel3Id,fileName,isDelete "
					+ "from easybuy_product where id=? " ;
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setObject(1, id);
			rs = pstmt.executeQuery();
			
			if (rs.next()) {
				product = new Product();
		        product.setId(rs.getInt("id"));
		        product.setName(rs.getString("name"));
		        product.setDescription(rs.getString("description"));
		        product.setPrice(rs.getFloat("price"));
		        product.setStock(rs.getInt("stock"));
		        product.setCategoryLevel1Id(rs.getInt("categoryLevel1Id"));
		        product.setCategoryLevel2Id(rs.getInt("categoryLevel2Id"));
		        product.setCategoryLevel3Id(rs.getInt("categoryLevel3Id"));
		        product.setFileName(rs.getString("fileName"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
        	super.closeAll(conn, pstmt, rs);
        }
		
		return product;
	}

	@Override
	public int subStock(int productId,int quantity) {
		int result=0;
    	try {
    		
    		conn = super.getConnection();
    		
    		String sql=" update easybuy_product set stock=stock-? where id=?  ";
    		
    		pstmt = conn.prepareStatement(sql);
    		
    		pstmt.setObject(1, quantity);
    		pstmt.setObject(2, productId);

    		result = pstmt.executeUpdate();
            
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
        	super.closeAll(conn, pstmt, rs);
        }
    	return result;
	}

	@Override
	public List<Product> findList(int startIndex, int pageSize) {
		List<Product> list = new ArrayList<>();
		try {
			conn = super.getConnection();
			String sql = "select id,name,description,price,stock,categoryLevel1Id,categoryLevel2Id,categoryLevel3Id,fileName,isDelete "
					+ "from easybuy_product where isDelete=0 " ;
			sql += " limit ?,?";
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setObject(1, startIndex);
			pstmt.setObject(2, pageSize);
			rs = pstmt.executeQuery();
			
			while (rs.next()) {
				Product product = new Product();
		        product.setId(rs.getInt("id"));
		        product.setName(rs.getString("name"));
		        product.setDescription(rs.getString("description"));
		        product.setPrice(rs.getFloat("price"));
		        product.setStock(rs.getInt("stock"));
		        product.setCategoryLevel1Id(rs.getInt("categoryLevel1Id"));
		        product.setCategoryLevel2Id(rs.getInt("categoryLevel2Id"));
		        product.setCategoryLevel3Id(rs.getInt("categoryLevel3Id"));
		        product.setFileName(rs.getString("fileName"));
				list.add(product);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
        	super.closeAll(conn, pstmt, rs);
        }
		return list;
	}

	@Override
	public int findCount() {
		int count=0;
		try {
			conn = super.getConnection();
			String sql = "select count(1) "
					+ "from easybuy_product where isDelete=0 " ;
			
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			
			if (rs.next()) {
				count = rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
        	super.closeAll(conn, pstmt, rs);
        }
		
		return count;
	}

}
